﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using MandalaySoft.Common;
using System.Data.SqlClient;
using System.Data;

namespace SIMS.BL
{
    public class StudentDB
    {
        MyDatabase _db = new MyDatabase();

        public DataTable GetStudentList(int SchoolID, out MyError error)
        {
            DataTable _dt = new DataTable("tblStudent");
            SortedList _paramList = new SortedList();
            _paramList.Add("@SchoolID", SchoolID);

            _dt = _db.GetDataTable("sprocStudentSelectItemList", CommandType.StoredProcedure, _paramList, out error);
            return _dt;
        }

        public int Delete(int StudentID, out MyError error)
        {
            SortedList _paramList = new SortedList();
            _paramList.Add("@StudentID", StudentID);

            return _db.ExecuteNonSql("sprocStudentDeleteSingleItem", CommandType.StoredProcedure, _paramList, out error);
        }

        public Student GetItem(int StudentID)
        {
            Student item = new Student();

            SortedList _paraList = new SortedList();
            _paraList.Add("@StudentID", StudentID);

            MyError _error = new MyError();
            SqlDataReader _reader = _db.GetSqlDataReader("sprocStudentSelectSingleItem", System.Data.CommandType.StoredProcedure, _paraList, out _error);
            item.Error = _error;

            if (_error.ErrorNo == 0 && _reader.Read())
            {
                item = FillData(_reader, item);
            }

            return item;
        }

        public Student Save(Student item)
        {
            try
            {
                SortedList _paraList = new SortedList();
                _paraList.Add("@StudentID", item.StudentID);
                _paraList.Add("AltID", item.AltID);
                _paraList.Add("UserID", item.UserID);
                _paraList.Add("FirstName", item.FirstName);
                _paraList.Add("LastName", item.LastName);
                _paraList.Add("MiddleName", item.MiddleName);
                _paraList.Add("Suffix", item.Suffix);
                _paraList.Add("EstGradDate", item.EstGraduateDate);
                _paraList.Add("Gender", item.Gender);
                _paraList.Add("CommonName", item.CommonName);
                _paraList.Add("BirthDate", item.BirthDate);
                _paraList.Add("PrimaryLangID", item.PrimaryLangID);
                _paraList.Add("@Email", item.Email);
                _paraList.Add("@PhoneNo", item.PhoneNo);
                _paraList.Add("@IsActive", item.IsActive);
                _paraList.Add("@CreatedBy", item.CreatedBy);
                _paraList.Add("@ModifiedBy", item.ModifiedBy);

                MyError _error = new MyError();
                Int64 _retValue = _db.Execute("sprocStudentInsertUpdateSingleItem", System.Data.CommandType.StoredProcedure, _paraList, out _error);
                item.Error = _error;

                if (_retValue > 0)
                {
                    item.StudentID = Convert.ToInt32(_retValue);
                    item.Error.ErrorMessage = "Saved successfully!";
                }
                else
                {
                    if (_error.ErrorNo == 0)
                    {
                        item.Error.ErrorNo = 10002;
                        item.Error.ErrorLocation = "Student";
                        item.Error.ErrorMessage = "No row affected.";
                    }
                }

            }
            catch (Exception Ex)
            {
                item.Error.ErrorNo = 10001;
                item.Error.ErrorLocation = "Student";
                item.Error.ErrorMessage = Ex.Message;
                item.Error.ErrorStack = Ex.StackTrace;

                MyError.CreateExceptionLog(Ex, "Student");
            }

            return item;
        }

        private Student FillData(IDataReader aRow, Student item)
        {
            try
            {
                item = new Student();
                item.StudentID = aRow.GetInt32(aRow.GetOrdinal("StudentID"));
                if (aRow["AltID"] != DBNull.Value)
                {
                    item.AltID = aRow.GetString(aRow.GetOrdinal("AltID"));
                }
                if (aRow["UserID"] != DBNull.Value)
                {
                    item.UserID = aRow.GetInt32(aRow.GetOrdinal("UserID"));
                }
                if (aRow["FirstName"] != DBNull.Value)
                {
                    item.FirstName = aRow.GetString(aRow.GetOrdinal("FirstName"));
                }
                if (aRow["LastName"] != DBNull.Value)
                {
                    item.LastName = aRow.GetString(aRow.GetOrdinal("LastName"));
                }
                if (aRow["MiddleName"] != DBNull.Value)
                {
                    item.MiddleName = aRow.GetString(aRow.GetOrdinal("MiddleName"));
                }
                if (aRow["Suffix"] != DBNull.Value)
                {
                    item.Suffix = aRow.GetString(aRow.GetOrdinal("Suffix"));
                }
                if (aRow["EstGradDate"] != DBNull.Value)
                {
                    item.EstGraduateDate = aRow.GetDateTime(aRow.GetOrdinal("EstGradDate"));
                }
                if (aRow["Gender"] != DBNull.Value)
                {
                    item.Gender = aRow.GetString(aRow.GetOrdinal("Gender"));
                }
                if (aRow["CommonName"] != DBNull.Value)
                {
                    item.CommonName = aRow.GetString(aRow.GetOrdinal("CommonName"));
                }
                if (aRow["BirthDate"] != DBNull.Value)
                {
                    item.BirthDate = aRow.GetDateTime(aRow.GetOrdinal("BirthDate"));
                }
                if (aRow["PrimaryLangID"] != DBNull.Value)
                {
                    item.PrimaryLangID = aRow.GetInt16(aRow.GetOrdinal("PrimaryLangID"));
                }
                if (aRow["Email"] != DBNull.Value)
                {
                    item.Email = aRow.GetString(aRow.GetOrdinal("Email"));
                }

                if (aRow["PhoneNo"] != DBNull.Value)
                {
                    item.PhoneNo = aRow.GetString(aRow.GetOrdinal("PhoneNo"));
                }

                item.IsActive = aRow.GetBoolean(aRow.GetOrdinal("IsActive"));
                item.CreatedBy = aRow.GetInt32(aRow.GetOrdinal("CreatedBy"));
                if (_db.ColumnExists(aRow, "CreatedByName") && aRow["CreatedByName"] != DBNull.Value)
                {
                    item.CreatedByName = aRow.GetString(aRow.GetOrdinal("CreatedByName"));
                }
                item.CreatedDate = aRow.GetDateTime(aRow.GetOrdinal("CreatedDate"));
                item.ModifiedBy = aRow.GetInt32(aRow.GetOrdinal("ModifiedBy"));
                if (_db.ColumnExists(aRow, "ModifiedByName") && aRow["ModifiedByName"] != DBNull.Value)
                {
                    item.ModifiedByName = aRow.GetString(aRow.GetOrdinal("ModifiedByName"));
                }
                item.ModifiedDate = aRow.GetDateTime(aRow.GetOrdinal("ModifiedDate"));
            }
            catch (Exception Ex)
            {
                item.Error.ErrorNo = 10003;
                item.Error.ErrorLocation = "Student";
                item.Error.ErrorMessage = Ex.Message;
                item.Error.ErrorStack = Ex.StackTrace;

                MyError.CreateExceptionLog(Ex, "Student");
            }

            return item;
        }
    }
}
